﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using DTO;


namespace DAO
{
    public class ThanhPham_DAO
    {
        public DataProvider _provider = new DataProvider();
        public ThanhPham_DAO()
        {
            _provider.connect();
        }
        public DataTable GetCmbLoaiTP()
        {
            string s = "Select * from LoaiSP where PhanBiet = 1 ";
            return _provider.executeQuery(s);
        }
       
        public DataTable GetDanhSachTP()
        {
            string s = "Select ThanhPham.MaTP,ThanhPham.TenTP,LoaiSP.TenLoai1,LoaiSP.MaLoai,ThanhPham.SoLuong "
                        + "from ThanhPham, LoaiSP "
                        + "where ThanhPham.MaLoaiTP = LoaiSP.MaLoai ";
            return _provider.executeQuery(s);
        }
        //Lấy DS Thành phẩm theo Xe thành phẩm truyền vào
        public DataTable GetDanhSachTPTheoXe(int MaXe)
        {
            string s = "Select ThanhPham.MaTP,ThanhPham.TenTP,LoaiSP.TenLoai1,LoaiSP.MaLoai,ThanhPham.SoLuong "
                        + "from ThanhPham, LoaiSP "
                        + "where ThanhPham.MaLoaiTP = LoaiSP.MaLoai and ThanhPham.MaLoaiTP = " + MaXe
                        + " order by ThanhPham.MaTP DESC";
            return _provider.executeQuery(s);
        }
      
        public void SuaThanhPham(ThanhPham_DTO ThanhPham)
        {
            string s = "UPDATE ThanhPham SET TenTP=N'" + ThanhPham.TenTP
                       + "',MaLoaiTP = " + ThanhPham.MaLoaiTP
                       + " ,SoLuong = " + ThanhPham.MaLoaiTP

                       + " WHERE MaTP = " + ThanhPham.MaTP;
            _provider.executeNonQuey(s);
        }
        //CT_ThanhPham
        // Lấy danh sách Chi tiết TP theo Tên TP và theo ngày tạo

        public DataTable GetDanhSachCT_ThanhPham(DateTime NgayTao)
        {
            string s = "Select ThanhPham.TenTP, CT_ThanhPham.GiaGoc, CT_ThanhPham.SoLuong,CT_ThanhPham.NgayTao,CT_ThanhPham.MaTP "
                        + " from CT_ThanhPham, ThanhPham"
                        + " where CT_ThanhPham.MaTP = ThanhPham.MaTP "
                        + " and CT_ThanhPham.NgayTao = '" + NgayTao + "'";
                        
            return _provider.executeQuery(s);
        }
        //Thêm vào bảng CT_ThanhPham
        public void ThemCT_ThanhPham(ThanhPham_DTO TP)
        {
            string s = "insert into CT_ThanhPham(MaTP,SoLuong,NgayTao,GiaGoc)"
                        + "values('" + TP.MaTP + "'," + TP.SoLuong + ",'" + TP.NgayTao + "'," + TP.GiaGoc + ")";

            _provider.executeNonQuey(s);
        }
        
        //Tăng số lượng ThanhPham trong bảng ThanhPham
        public void Tang_SoLuongThanhPham(float SL_TP,string MaTP)
        {
            string s = " update ThanhPham"
                        + " set SoLuong = (select sum(SoLuong+" + SL_TP + ")"
                                       + " from ThanhPham"
                                       + " where MaTP = '" + MaTP + "')"
                        + " where MaTP = '" + MaTP + "'";

            _provider.executeNonQuey(s);
        }
       public void Pro_Update_NLG(string MaNLG,string MaTP, float SoLuongTuManHinh)
        {
           
            _provider.Pro_Update_NLG(MaNLG,MaTP,SoLuongTuManHinh);
        }
       public DataTable CT_ThanhPham(string MaTP)
       {
           string s = "select NLG.TenNLG, CT_SX_ThanhPham.SoLuongSX, NLG.DVT, NLG.MaNLG"
                       + " from CT_SX_ThanhPham, NLG"
                       + " where CT_SX_ThanhPham.MaNLG = NLG.MaNLG"
                       + " and CT_SX_ThanhPham.MaTP ='" + MaTP + "'";

           return _provider.executeQuery(s);
       }
        //Thông tin nguyên liệu của TP
       //public void Them_ThongTinNLCuaTP(CT_SX_ThanhPham_DTO dto)
       //{
       //    string s = "insert into CT_SX_ThanhPham(MaTP,MaNLG,NgayTao,SoLuongNL)"
       //                + "values('"
       //                + dto.MaTP + "'," 
       //                + dto.MaNLG + ",'" 
       //                + dto.NgayTao+ "'," 
       //                + dto.SoLuongNL+ ")";

       //    _provider.executeNonQuey(s);
       //}
       public DataTable Get_ThongTinNLCuaTP(string MaTP)
       {
           string s = "Select ThanhPham.TenTP, NLG.TenNLG, CT_SX_ThanhPham.SoLuongNL, CT_SX_ThanhPham.NgayTao"
                        + " from CT_SX_ThanhPham,ThanhPham, NLG where CT_SX_ThanhPham.MaTP=ThanhPham.MaTP"
                                                                    + " and CT_SX_ThanhPham.MaNLG = NLG.MaNLG"
                                                                    + " and CT_SX_ThanhPham.MaTP = '" + MaTP + "'";
           return _provider.executeQuery(s);
           
       }
        //Them thanh pham ban dau
       public int proc_Ins_ThanhPham(ThanhPham_DTO dto)
       {
           return _provider.proc_Ins_ThanhPham(dto);
       }
        //Them so luong TP hang ngay
       public void proc_Ins_SoLuongThanhPhamMoiNgay(ThanhPham_DTO dto)
       {
            _provider.proc_Ins_SoLuongThanhPhamMoiNgay(dto);
       }
        //Them NL cho TP
        //Lay du lieu cho cmb_TP_NGTheoLoaiTP
       public DataTable GetDanhSachTPTheoMaLoaiTP(int MaLoaiTP)
       {
           string s = "Select MaTP,TenTP "
                       + "from ThanhPham"
                       + " where ThanhPham.MaLoaiTP = " + MaLoaiTP
                       + " order by ThanhPham.MaTP DESC";
           return _provider.executeQuery(s);
       }
        //Lay du tat ca cac NL
       public DataTable GetDanhSachNL()
       {
           string s = "Select MaNLG,TenNLG, DVT "
                       + "from NLG"
                       + " order by TenNLG DESC";
           return _provider.executeQuery(s);
       }
    }
}
